﻿using Meiam.System.Interfaces.IService;
using Meiam.System.Model.Entity;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Meiam.System.Interfaces.Service
{
    public class CutPieceEfficiencySummaryService : SqlSugarBase<CutPieceEfficiencySummary>, ICutPieceEfficiencySummaryService
    {
        public CutPieceEfficiencySummaryService(IConfiguration _configuration)
        {
            base.connectionString = _configuration.GetConnectionString("DB3");
        }

        #region 切片效率推送
        public List<CutPieceEfficiencySummary> GetCutPieceEfficiencySummaryData4H()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-4);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"select t.WorkShop,count(t.DH) CutNum,SUM(t.CZYS) CCYS,SUM(t.YCYS) YCYS,SUM(t.XLYS) XLYS,ROUND(SUM(t.CZYS)/count(t.DH),1) DDCZYS,ROUND(SUM(t.YCYS)/count(t.DH),1) DDYCYS,ROUND(SUM(t.XLYS)/count(t.DH),1) DDXLYS from (
with tab as(
         select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,substr(t1.JBNUMBER,6,3) spec,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')  ),
   tab2 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') ),
   tab3 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') )

   select DGW.WORKSHOP||tab.spec as WorkShop,DGW.MACHINETYPE MachineType,tab.MACHINENO MachineNo,tab.OPRATION Opration
         ,E.DESCRIPTION OprationName,tab.Jbnumber DH,tab.spec,      
         tab.name,tab3.stime ptime,tab.stime stime,tab2.stime etime,tab2.f2 dxcs,tab.id,tab.machineno, 
         (case when cast(to_char(tab.CreateTime,'hh24') as int )<8
       then DS.shift_yesterday
       when cast(to_char(tab.CreateTime,'hh24') as int )<20
       then DS.shift_day
       else DS.shift_night end) 班次 ,  
        ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 ) as CZYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 ) as DDYS,
        ROUND(tab.processing_time2,0) as GONGYIYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as YCYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as XLYS
         from tab 
         left join tab2 on tab.rk=tab2.rk-1 and tab.machineno=tab2.machineno 
         left join tab3 on tab.rk=tab3.rk+1 and tab.machineno=tab3.machineno
         left JOIN DATA_SHIFT DS ON TO_CHAR(DS.SHIFT_TIME,'YYYYMMDD') = TO_CHAR(tab.CREATETIME,'YYYYMMDD')
         left JOIN ZHWIPUSER.Employee E ON E.EMPLOYEENAME = tab.OPRATION
         left JOIN DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = tab.MACHINENO
         where tab.name='Process_strart'
     order by tab.MACHINENO ,tab.stime
     )t group by WorkShop
     UNION ALL 
     select t.WorkShop,count(t.DH),SUM(t.CZYS),SUM(t.YCYS),SUM(t.XLYS),ROUND(SUM(t.CZYS)/count(t.DH),1) DDCZYS,ROUND(SUM(t.YCYS)/count(t.DH),1) DDYCYS,ROUND(SUM(t.XLYS)/count(t.DH),1) DDXLYS from (
with tab as(
         select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,substr(t1.JBNUMBER,6,3) spec,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')  ),
   tab2 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') ),
   tab3 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') )

   select substr(DGW.WORKSHOP,0,3)||'合计'  as WorkShop,DGW.MACHINETYPE MachineType,tab.MACHINENO MachineNo,tab.OPRATION Opration
         ,E.DESCRIPTION OprationName,tab.Jbnumber DH,tab.spec,      
         tab.name,tab3.stime ptime,tab.stime stime,tab2.stime etime,tab2.f2 dxcs,tab.id,tab.machineno, 
         (case when cast(to_char(tab.CreateTime,'hh24') as int )<8
       then DS.shift_yesterday
       when cast(to_char(tab.CreateTime,'hh24') as int )<20
       then DS.shift_day
       else DS.shift_night end) 班次 ,  
        ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 ) as CZYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 ) as DDYS,
        ROUND(tab.processing_time2,0) as GONGYIYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as YCYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as XLYS
         from tab 
         left join tab2 on tab.rk=tab2.rk-1 and tab.machineno=tab2.machineno 
         left join tab3 on tab.rk=tab3.rk+1 and tab.machineno=tab3.machineno
         left JOIN DATA_SHIFT DS ON TO_CHAR(DS.SHIFT_TIME,'YYYYMMDD') = TO_CHAR(tab.CREATETIME,'YYYYMMDD')
         left JOIN ZHWIPUSER.Employee E ON E.EMPLOYEENAME = tab.OPRATION
         left JOIN DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = tab.MACHINENO
         where tab.name='Process_strart'
     order by tab.MACHINENO ,tab.stime
     )t group by WorkShop order by WorkShop   
 ";
            return base.Query(sql);
        }
        public List<CutPieceEfficiencySummary> GetCutPieceEfficiencySummaryData8H()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-8);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"select t.WorkShop,count(t.DH) CutNum,SUM(t.CZYS) CCYS,SUM(t.YCYS) YCYS,SUM(t.XLYS) XLYS,ROUND(SUM(t.CZYS)/count(t.DH),1) DDCZYS,ROUND(SUM(t.YCYS)/count(t.DH),1) DDYCYS,ROUND(SUM(t.XLYS)/count(t.DH),1) DDXLYS from (
with tab as(
         select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,substr(t1.JBNUMBER,6,3) spec,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')  ),
   tab2 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') ),
   tab3 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') )

   select DGW.WORKSHOP||tab.spec as WorkShop,DGW.MACHINETYPE MachineType,tab.MACHINENO MachineNo,tab.OPRATION Opration
         ,E.DESCRIPTION OprationName,tab.Jbnumber DH,tab.spec,      
         tab.name,tab3.stime ptime,tab.stime stime,tab2.stime etime,tab2.f2 dxcs,tab.id,tab.machineno, 
         (case when cast(to_char(tab.CreateTime,'hh24') as int )<8
       then DS.shift_yesterday
       when cast(to_char(tab.CreateTime,'hh24') as int )<20
       then DS.shift_day
       else DS.shift_night end) 班次 ,  
        ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 ) as CZYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 ) as DDYS,
        ROUND(tab.processing_time2,0) as GONGYIYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as YCYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as XLYS
         from tab 
         left join tab2 on tab.rk=tab2.rk-1 and tab.machineno=tab2.machineno 
         left join tab3 on tab.rk=tab3.rk+1 and tab.machineno=tab3.machineno
         left JOIN DATA_SHIFT DS ON TO_CHAR(DS.SHIFT_TIME,'YYYYMMDD') = TO_CHAR(tab.CREATETIME,'YYYYMMDD')
         left JOIN ZHWIPUSER.Employee E ON E.EMPLOYEENAME = tab.OPRATION
         left JOIN DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = tab.MACHINENO
         where tab.name='Process_strart'
     order by tab.MACHINENO ,tab.stime
     )t group by WorkShop
     UNION ALL 
     select t.WorkShop,count(t.DH),SUM(t.CZYS),SUM(t.YCYS),SUM(t.XLYS),ROUND(SUM(t.CZYS)/count(t.DH),1) DDCZYS,ROUND(SUM(t.YCYS)/count(t.DH),1) DDYCYS,ROUND(SUM(t.XLYS)/count(t.DH),1) DDXLYS from (
with tab as(
         select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,substr(t1.JBNUMBER,6,3) spec,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')  ),
   tab2 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') ),
   tab3 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') )

   select substr(DGW.WORKSHOP,0,3)||'合计'  as WorkShop,DGW.MACHINETYPE MachineType,tab.MACHINENO MachineNo,tab.OPRATION Opration
         ,E.DESCRIPTION OprationName,tab.Jbnumber DH,tab.spec,      
         tab.name,tab3.stime ptime,tab.stime stime,tab2.stime etime,tab2.f2 dxcs,tab.id,tab.machineno, 
         (case when cast(to_char(tab.CreateTime,'hh24') as int )<8
       then DS.shift_yesterday
       when cast(to_char(tab.CreateTime,'hh24') as int )<20
       then DS.shift_day
       else DS.shift_night end) 班次 ,  
        ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 ) as CZYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 ) as DDYS,
        ROUND(tab.processing_time2,0) as GONGYIYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as YCYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as XLYS
         from tab 
         left join tab2 on tab.rk=tab2.rk-1 and tab.machineno=tab2.machineno 
         left join tab3 on tab.rk=tab3.rk+1 and tab.machineno=tab3.machineno
         left JOIN DATA_SHIFT DS ON TO_CHAR(DS.SHIFT_TIME,'YYYYMMDD') = TO_CHAR(tab.CREATETIME,'YYYYMMDD')
         left JOIN ZHWIPUSER.Employee E ON E.EMPLOYEENAME = tab.OPRATION
         left JOIN DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = tab.MACHINENO
         where tab.name='Process_strart'
     order by tab.MACHINENO ,tab.stime
     )t group by WorkShop order by WorkShop   
 ";
            return base.Query(sql);
        }

        public List<CutPieceEfficiencySummary> GetCutPieceEfficiencySummaryData12H()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-12);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"select t.WorkShop,count(t.DH) CutNum,SUM(t.CZYS) CCYS,SUM(t.YCYS) YCYS,SUM(t.XLYS) XLYS,ROUND(SUM(t.CZYS)/count(t.DH),1) DDCZYS,ROUND(SUM(t.YCYS)/count(t.DH),1) DDYCYS,ROUND(SUM(t.XLYS)/count(t.DH),1) DDXLYS from (
with tab as(
         select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,substr(t1.JBNUMBER,6,3) spec,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')  ),
   tab2 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') ),
   tab3 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') )

   select DGW.WORKSHOP||tab.spec as WorkShop,DGW.MACHINETYPE MachineType,tab.MACHINENO MachineNo,tab.OPRATION Opration
         ,E.DESCRIPTION OprationName,tab.Jbnumber DH,tab.spec,      
         tab.name,tab3.stime ptime,tab.stime stime,tab2.stime etime,tab2.f2 dxcs,tab.id,tab.machineno, 
         (case when cast(to_char(tab.CreateTime,'hh24') as int )<8
       then DS.shift_yesterday
       when cast(to_char(tab.CreateTime,'hh24') as int )<20
       then DS.shift_day
       else DS.shift_night end) 班次 ,  
        ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 ) as CZYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 ) as DDYS,
        ROUND(tab.processing_time2,0) as GONGYIYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as YCYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as XLYS
         from tab 
         left join tab2 on tab.rk=tab2.rk-1 and tab.machineno=tab2.machineno 
         left join tab3 on tab.rk=tab3.rk+1 and tab.machineno=tab3.machineno
         left JOIN DATA_SHIFT DS ON TO_CHAR(DS.SHIFT_TIME,'YYYYMMDD') = TO_CHAR(tab.CREATETIME,'YYYYMMDD')
         left JOIN ZHWIPUSER.Employee E ON E.EMPLOYEENAME = tab.OPRATION
         left JOIN DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = tab.MACHINENO
         where tab.name='Process_strart'
     order by tab.MACHINENO ,tab.stime
     )t group by WorkShop
     UNION ALL 
     select t.WorkShop,count(t.DH),SUM(t.CZYS),SUM(t.YCYS),SUM(t.XLYS),ROUND(SUM(t.CZYS)/count(t.DH),1) DDCZYS,ROUND(SUM(t.YCYS)/count(t.DH),1) DDYCYS,ROUND(SUM(t.XLYS)/count(t.DH),1) DDXLYS from (
with tab as(
         select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,substr(t1.JBNUMBER,6,3) spec,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')  ),
   tab2 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') ),
   tab3 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') )

   select substr(DGW.WORKSHOP,0,3)||'合计'  as WorkShop,DGW.MACHINETYPE MachineType,tab.MACHINENO MachineNo,tab.OPRATION Opration
         ,E.DESCRIPTION OprationName,tab.Jbnumber DH,tab.spec,      
         tab.name,tab3.stime ptime,tab.stime stime,tab2.stime etime,tab2.f2 dxcs,tab.id,tab.machineno, 
         (case when cast(to_char(tab.CreateTime,'hh24') as int )<8
       then DS.shift_yesterday
       when cast(to_char(tab.CreateTime,'hh24') as int )<20
       then DS.shift_day
       else DS.shift_night end) 班次 ,  
        ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 ) as CZYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 ) as DDYS,
        ROUND(tab.processing_time2,0) as GONGYIYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as YCYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as XLYS
         from tab 
         left join tab2 on tab.rk=tab2.rk-1 and tab.machineno=tab2.machineno 
         left join tab3 on tab.rk=tab3.rk+1 and tab.machineno=tab3.machineno
         left JOIN DATA_SHIFT DS ON TO_CHAR(DS.SHIFT_TIME,'YYYYMMDD') = TO_CHAR(tab.CREATETIME,'YYYYMMDD')
         left JOIN ZHWIPUSER.Employee E ON E.EMPLOYEENAME = tab.OPRATION
         left JOIN DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = tab.MACHINENO
         where tab.name='Process_strart'
     order by tab.MACHINENO ,tab.stime
     )t group by WorkShop order by WorkShop   
 ";
            return base.Query(sql);
        }

        public List<CutPieceEfficiencySummary> GetCutPieceEfficiencySummaryData24H()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-24);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"select t.WorkShop,count(t.DH) CutNum,SUM(t.CZYS) CCYS,SUM(t.YCYS) YCYS,SUM(t.XLYS) XLYS,ROUND(SUM(t.CZYS)/count(t.DH),1) DDCZYS,ROUND(SUM(t.YCYS)/count(t.DH),1) DDYCYS,ROUND(SUM(t.XLYS)/count(t.DH),1) DDXLYS from (
with tab as(
         select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,substr(t1.JBNUMBER,6,3) spec,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')  ),
   tab2 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') ),
   tab3 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') )

   select DGW.WORKSHOP||tab.spec as WorkShop,DGW.MACHINETYPE MachineType,tab.MACHINENO MachineNo,tab.OPRATION Opration
         ,E.DESCRIPTION OprationName,tab.Jbnumber DH,tab.spec,      
         tab.name,tab3.stime ptime,tab.stime stime,tab2.stime etime,tab2.f2 dxcs,tab.id,tab.machineno, 
         (case when cast(to_char(tab.CreateTime,'hh24') as int )<8
       then DS.shift_yesterday
       when cast(to_char(tab.CreateTime,'hh24') as int )<20
       then DS.shift_day
       else DS.shift_night end) 班次 ,  
        ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 ) as CZYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 ) as DDYS,
        ROUND(tab.processing_time2,0) as GONGYIYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as YCYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as XLYS
         from tab 
         left join tab2 on tab.rk=tab2.rk-1 and tab.machineno=tab2.machineno 
         left join tab3 on tab.rk=tab3.rk+1 and tab.machineno=tab3.machineno
         left JOIN DATA_SHIFT DS ON TO_CHAR(DS.SHIFT_TIME,'YYYYMMDD') = TO_CHAR(tab.CREATETIME,'YYYYMMDD')
         left JOIN ZHWIPUSER.Employee E ON E.EMPLOYEENAME = tab.OPRATION
         left JOIN DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = tab.MACHINENO
         where tab.name='Process_strart'
     order by tab.MACHINENO ,tab.stime
     )t group by WorkShop
     UNION ALL 
     select t.WorkShop,count(t.DH),SUM(t.CZYS),SUM(t.YCYS),SUM(t.XLYS),ROUND(SUM(t.CZYS)/count(t.DH),1) DDCZYS,ROUND(SUM(t.YCYS)/count(t.DH),1) DDYCYS,ROUND(SUM(t.XLYS)/count(t.DH),1) DDXLYS from (
with tab as(
         select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,substr(t1.JBNUMBER,6,3) spec,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')  ),
   tab2 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') ),
   tab3 as (select rank()over(partition by t1.machineno order by t1.createtime) rk
         ,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.* 
         from DATA_QPJ_WORKFLOW t1
         where  t1.f1='1' and  t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  and t1.createtime<to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') )

   select substr(DGW.WORKSHOP,0,3)||'合计'  as WorkShop,DGW.MACHINETYPE MachineType,tab.MACHINENO MachineNo,tab.OPRATION Opration
         ,E.DESCRIPTION OprationName,tab.Jbnumber DH,tab.spec,      
         tab.name,tab3.stime ptime,tab.stime stime,tab2.stime etime,tab2.f2 dxcs,tab.id,tab.machineno, 
         (case when cast(to_char(tab.CreateTime,'hh24') as int )<8
       then DS.shift_yesterday
       when cast(to_char(tab.CreateTime,'hh24') as int )<20
       then DS.shift_day
       else DS.shift_night end) 班次 ,  
        ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 ) as CZYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 ) as DDYS,
        ROUND(tab.processing_time2,0) as GONGYIYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(TO_NUMBER(tab.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as YCYS,
        ROUND(TO_NUMBER(tab2.CreateTime-tab3.createtime)* 24 * 60 )-ROUND(tab.processing_time2,0) as XLYS
         from tab 
         left join tab2 on tab.rk=tab2.rk-1 and tab.machineno=tab2.machineno 
         left join tab3 on tab.rk=tab3.rk+1 and tab.machineno=tab3.machineno
         left JOIN DATA_SHIFT DS ON TO_CHAR(DS.SHIFT_TIME,'YYYYMMDD') = TO_CHAR(tab.CREATETIME,'YYYYMMDD')
         left JOIN ZHWIPUSER.Employee E ON E.EMPLOYEENAME = tab.OPRATION
         left JOIN DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = tab.MACHINENO
         where tab.name='Process_strart'
     order by tab.MACHINENO ,tab.stime
     )t group by WorkShop order by WorkShop   
 ";
            return base.Query(sql);
        }

        #endregion
    }
}
